package excel

import (
	"fmt"

	"github.com/pkg/errors"
	"github.com/xuri/excelize/v2"
)

func readBackEndRecordFile(filePath string) (dbRecords []*UserDBRecord, err error) {
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		println(err.Error())
		return
	}
	rows, err := f.GetRows("Sheet1")
	if err != nil {
		return
	}
	for i := 1; i < len(rows); i++ {
		var cur = new(UserDBRecord)
		cur.Id = rows[i][0]
		cur.Uid = rows[i][1]
		cur.DeviceId = rows[i][2]
		cur.Vid = rows[i][3]
		cur.Locale = rows[i][4]
		cur.PromotionId = rows[i][5]
		cur.Status = rows[i][6]
		cur.PrizeType = rows[i][7]
		cur.RequestId = rows[i][8]
		cur.GroupId = rows[i][9]
		cur.RedeemCode = rows[i][10]
		cur.RedeemMessage = rows[i][11]
		cur.CreateTime = rows[i][12]
		cur.DatachangeLasttime = rows[i][13]
		dbRecords = append(dbRecords, cur)
	}
	return
}

func readBIRecordFile(filePath string) (biRecords []*BIRecord, err error) {
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		println(err.Error())
		return
	}
	rows, err := f.GetRows("Sheet1")
	if err != nil {
		return
	}
	for i := 1; i < len(rows); i++ {
		var cur = new(BIRecord)
		cur.PromotionId = rows[i][0]
		cur.Name = rows[i][1]
		cur.OrderDate = rows[i][2]
		cur.HotelNewUserOrders = rows[i][3]
		cur.OrderId = rows[i][4]
		cur.Uid = rows[i][5]
		cur.OrdersStatus = rows[i][6]
		cur.Locale = rows[i][7]
		cur.ClaimTime = rows[i][8]
		biRecords = append(biRecords, cur)
	}
	return
}

var SheetName = "Sheet1"

func WriteUidToFile(dbRecords []*UserDBRecord, biRecords []*BIRecord, fileName string) error {
	if len(dbRecords) <= 0 || len(biRecords) <= 0 {
		return errors.New("record is null")
	}

	var dbMap = make(map[string][]*UserDBRecord)
	for _, record := range dbRecords {
		if curDbs, ok := dbMap[record.Uid]; ok {
			curDbs = append(curDbs, record)
			dbMap[record.Uid] = curDbs
		} else {
			dbMap[record.Uid] = []*UserDBRecord{record}
		}
	}

	f := excelize.NewFile()
	// 创建一个工作表
	index := f.NewSheet(SheetName)
	// 设置表头
	initTitle(f)
	// 单元格行号
	var idx = 2
	// 遍历每一个参与者
	for _, bi := range biRecords {
		if curDbs, ok := dbMap[bi.Uid]; ok {
			for _, db := range curDbs {
				if bi.PromotionId == db.PromotionId {
					sum := &SumRecord{
						DbRecord: db,
						BIRecord: bi,
					}
					setLineValue(f, idx, sum)
					idx++
				}
			}
		}
	}

	// 设置工作簿的默认工作表
	f.SetActiveSheet(index)
	// 根据指定路径保存文件
	if err := f.SaveAs(fileName); err != nil {
		return err
	}
	return nil
}

func initTitle(f *excelize.File) {
	f.SetCellValue(SheetName, "A1", "promotion_id")
	f.SetCellValue(SheetName, "B1", "name")
	f.SetCellValue(SheetName, "C1", "orderdate")
	f.SetCellValue(SheetName, "D1", "hotel_new_user_orders")
	f.SetCellValue(SheetName, "E1", "orderid")
	f.SetCellValue(SheetName, "F1", "uid")
	f.SetCellValue(SheetName, "G1", "orderstatus")
	f.SetCellValue(SheetName, "H1", "locale")
	f.SetCellValue(SheetName, "I1", "claim_time")
	f.SetCellValue(SheetName, "J1", "id")
	f.SetCellValue(SheetName, "K1", "uid")
	f.SetCellValue(SheetName, "L1", "device_id")
	f.SetCellValue(SheetName, "M1", "vid")
	f.SetCellValue(SheetName, "N1", "locale")
	f.SetCellValue(SheetName, "O1", "promotion_id")
	f.SetCellValue(SheetName, "P1", "status")
	f.SetCellValue(SheetName, "Q1", "prize_type")
	f.SetCellValue(SheetName, "R1", "request_id")
	f.SetCellValue(SheetName, "S1", "group_id")
	f.SetCellValue(SheetName, "T1", "redeem_code")
	f.SetCellValue(SheetName, "U1", "redeem_message")
	f.SetCellValue(SheetName, "V1", "create_time")
	f.SetCellValue(SheetName, "W1", "datachange_lasttime")
}

func setLineValue(f *excelize.File, idx int, sumRecord *SumRecord) error {
	b := sumRecord.BIRecord
	db := sumRecord.DbRecord
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "A", idx), b.PromotionId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "B", idx), b.Name)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "C", idx), b.OrderDate)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "D", idx), b.HotelNewUserOrders)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "E", idx), b.OrderId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "F", idx), b.Uid)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "G", idx), b.OrdersStatus)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "H", idx), b.Locale)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "I", idx), b.ClaimTime)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "J", idx), db.Id)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "K", idx), db.Uid)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "L", idx), db.DeviceId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "M", idx), db.Vid)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "N", idx), db.Locale)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "O", idx), db.PromotionId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "P", idx), db.Status)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "Q", idx), db.PrizeType)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "R", idx), db.RequestId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "S", idx), db.GroupId)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "T", idx), db.RedeemCode)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "U", idx), db.RedeemMessage)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "V", idx), db.CreateTime)
	f.SetCellValue(SheetName, fmt.Sprintf("%s%d", "W", idx), db.DatachangeLasttime)

	return nil
}

type SumRecord struct {
	DbRecord *UserDBRecord
	BIRecord *BIRecord
}

type BIRecord struct {
	PromotionId        string `json:"promotion_id"`
	Name               string `json:"name"`
	OrderDate          string `json:"orderdate"`
	HotelNewUserOrders string `json:"hotel_new_user_orders"`
	OrderId            string `json:"orderid"`
	Uid                string `json:"uid"`
	OrdersStatus       string `json:"orderstatus"`
	Locale             string `json:"locale"`
	ClaimTime          string `json:"claim_time"`
}

type UserDBRecord struct {
	Id                 string `json:"id"`
	Uid                string `json:"uid"`
	DeviceId           string `json:"device_id"`
	Vid                string `json:"vid"`
	Locale             string `json:"locale"`
	PromotionId        string `json:"promotion_id"`
	Status             string `json:"status"`
	PrizeType          string `json:"prize_type"`
	RequestId          string `json:"request_id"`
	GroupId            string `json:"group_id"`
	RedeemCode         string `json:"redeem_code"`
	RedeemMessage      string `json:"redeem_message"`
	CreateTime         string `json:"create_time"`
	DatachangeLasttime string `json:"datachange_lasttime"`
}
